package DBside;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import Utils.Concurrent.TaskRunError;
import datatype.DBtypes.FilmfromDB;
import datatype.DBtypes.ProgramfromDB;
import datatype.searchtype.SearchResult.MediaRes;
import datatype.searchtype.SearchResult.PersonRes;
import interfaces.MediaManagment;

/**
 * @author Yuval
 * 
 *         implement the MediaManagment interface, this class create and execute
 *         all the queries for the film, tv, actor and etc' tables in the DB
 * 
 */

public class MediaImplementation implements MediaManagment {

	@Override
	public List<MediaRes> getAllFilmStartWith(String query, Statement stm)
			throws TaskRunError {
		List<MediaRes> subfilms = new ArrayList<MediaRes>();
		try {
			ResultSet rs = stm
					.executeQuery("SELECT Id, Name, Release_Date from Film where Name like '"
							+ query + "%' order by Name limit 100");

			while (rs.next()) {
				subfilms.add(new MediaRes(rs.getInt("Id"),
						rs.getString("Name"), rs.getInt("Release_Date")));
			}
			rs.close();
		} catch (SQLException e) {
			throw new TaskRunError("Connection Error");
		}
		return subfilms;
	}

	@Override
	public List<MediaRes> getAllProgramStartWith(String query, Statement stm)
			throws TaskRunError {
		List<MediaRes> subProgram = new ArrayList<MediaRes>();
		try {
			ResultSet rs = stm
					.executeQuery("SELECT Id, Name, First_Episode_Date from TV_Program where lower(Name) like '"
							+ query.toLowerCase()
							+ "%' order by Name limit 100");
			String fDate = "";
			while (rs.next()) {
				subProgram.add(new MediaRes(rs.getInt("Id"), rs
						.getString("Name"), rs.getInt(3)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return subProgram;
	}

	@Override
	public List<PersonRes> getAllActorsStartWith(String query, Statement stm,
			boolean isFilm) throws TaskRunError {
		List<PersonRes> actors = new ArrayList<PersonRes>();
		try {
			String queryForDB = "";

			if (isFilm) {
				queryForDB = "(Select Id, Name, \"\" as known_for "
						+ "from Film_Actor "
						+ "where (Id, Name) not in (SELECT Film_Play.Actor_Id, Film_Actor.Name "
						+ "From Film_Play, Film_Actor "
						+ "where Film_Actor.Id = Film_Play.Actor_Id AND lower(Film_Actor.Name) like '"
						+ query.toLowerCase()
						+ "%') "
						+ "AND "
						+ "lower(Film_Actor.Name) like '"
						+ query.toLowerCase()
						+ "%') "
						+ "union  "
						+ "(SELECT Film_Play.Actor_Id, Film_Actor.Name, Film.Name as Known_for "
						+ "From Film_Play, Film_Actor, Film "
						+ "where Film_Actor.Id = Film_Play.Actor_Id AND Film.Id = Film_Play.Film_Id AND lower(Film_Actor.Name) like '"
						+ query.toLowerCase() + "%' "
						+ "group by Film_Actor.Id) order by Name limit 100 ";

			} else {

				queryForDB = "(Select Id, Name, \"\" as known_for "
						+ "from TV_Actor "
						+ "where (Id, Name) not in (SELECT TV_Play.Actor_Id, TV_Actor.Name "
						+ "From TV_Play, TV_Actor "
						+ "where TV_Actor.Id = TV_Play.Actor_Id AND lower(TV_Actor.Name) like '"
						+ query.toLowerCase()
						+ "%') "
						+ "AND "
						+ "lower(TV_Actor.Name) like '"
						+ query.toLowerCase()
						+ "%') "
						+ "union  "
						+ "(SELECT TV_Play.Actor_Id, TV_Actor.Name, TV_Program.Name as Known_for "
						+ "From TV_Play, TV_Actor, TV_Program "
						+ "where TV_Actor.Id = TV_Play.Actor_Id AND TV_Program.Id = TV_Play.Program_Id "
						+ "AND lower(TV_Actor.Name) like '"
						+ query.toLowerCase() + "%' "
						+ "group by TV_Actor.Id) order by Name limit 100";

			}
			System.out.println(queryForDB);
			ResultSet rs = stm.executeQuery(queryForDB);

			while (rs.next()) {
				actors.add(new PersonRes(rs.getInt(1), rs.getString(2), rs
						.getString(3)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return actors;
	}

	@Override
	public List<PersonRes> getAllCreatorStartWith(String query, Statement stm)
			throws TaskRunError {
		List<PersonRes> creators = new ArrayList<PersonRes>();
		try {
			ResultSet rs = stm
					.executeQuery(" (Select Id, Name, \"\" as known_for"
							+ " from Program_Creator"
							+ " where (Id, Name) not in (SELECT Created.Program_Creator_Id, Program_Creator.Name"
							+ " From Created, Program_Creator"
							+ " where Program_Creator.Id = Created.Program_Creator_Id AND lower(Program_Creator.Name) like '"
							+ query.toLowerCase()
							+ "%')"
							+ " AND"
							+ " lower(Program_Creator.Name) like '"
							+ query.toLowerCase()
							+ "%')"
							+ " union "
							+ " (SELECT Created.Program_Creator_Id, Program_Creator.Name, TV_Program.Name as Known_for"
							+ " From Created, Program_Creator, TV_Program"
							+ " where Program_Creator.Id = Created.Program_Creator_Id AND TV_Program.Id = Created.Program_Id AND lower(Program_Creator.Name) like '"
							+ query.toLowerCase()
							+ "%'"
							+ " group by Program_Creator.Id) order by Name limit 100");

			while (rs.next()) {
				creators.add(new PersonRes(rs.getInt(1), rs.getString(2), rs
						.getString(3)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return creators;
	}

	@Override
	public List<PersonRes> getAllDirectorStartWith(String query, Statement stm)
			throws TaskRunError {
		List<PersonRes> directors = new ArrayList<PersonRes>();
		try {
			ResultSet rs = stm
					.executeQuery("(Select Id, Name, \"\" as known_for "
							+ " from Director where (Id, Name) not in "
							+ " (SELECT Directs.Director_Id, Director.Name From Directs, Director"
							+ " where Director.Id = Directs.Director_Id AND lower(Director.Name) like '"
							+ query.toLowerCase()
							+ "%') AND "
							+ " lower(Director.Name) like '"
							+ query.toLowerCase()
							+ "%') union"
							+ " (SELECT Directs.Director_Id, Director.Name, Film.Name as Known_for"
							+ " From Directs, Director, Film where Director.Id = Directs.Director_Id AND Film.Id = Directs.Film_Id AND"
							+ " lower(Director.Name) like '"
							+ query.toLowerCase()
							+ "%' group by Director.Id) order by Name limit 100");

			while (rs.next()) {
				directors.add(new PersonRes(rs.getInt(1), rs.getString(2), rs
						.getString(3)));
			}

			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return directors;
	}

	@Override
	public ProgramfromDB getProgramFromDB(int id, Statement stm)
			throws TaskRunError {
		ProgramfromDB program = null;
		try {
			ResultSet rs = stm
					.executeQuery("Select Name, Origin_Country, Number_of_seasons ,Number_of_Episodes,First_Episode_Date, Final_Episode_Date from TV_Program where Id = "
							+ id);

			if (!rs.next())
				return null;

			program = new ProgramfromDB(id, rs.getString("Name"), rs.getInt(5),
					rs.getInt(6), 1, 1, 1, 1, rs.getInt("Number_of_Episodes"),
					rs.getInt("Number_of_seasons"),
					rs.getString("Origin_Country"));
			rs.close();

			rs = stm.executeQuery("Select Program_Creator.Id, Program_Creator.Name "
					+ "from Program_Creator, Created "
					+ "where Program_Creator.Id = Created.Program_Creator_Id "
					+ "AND Created.Program_Id = " + id);
			while (rs.next()) {
				program.addCreator(rs.getInt(1), rs.getString(2), true);
			}
			rs.close();

			rs = stm.executeQuery("Select TV_Actor.Id, TV_Actor.Name, TV_Play.Character "
					+ "from TV_Actor, TV_Play "
					+ "where TV_Actor.Id = TV_Play.Actor_Id "
					+ "AND TV_Play.Program_Id = " + id);
			while (rs.next()) {
				program.addActor(rs.getInt(1), rs.getString(2),
						rs.getString(3), 0, 0, true);
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return program;
	}

	@Override
	public FilmfromDB getFilmFromDB(int id, Statement stm) throws TaskRunError {
		FilmfromDB film = null;
		try {
			ResultSet rs = stm
					.executeQuery("Select Name, Release_Date, Sequel, Prequel,Country, Estimated_Value, Release_Date from Film where Id = "
							+ id);

			if (!rs.next())
				return null;
			String value = rs.getString(6);
			String subValue = "";
			for (char c : value.toCharArray()) {
				if (Character.isDigit(c))
					subValue += c;
				else
					break;
			}
			if (subValue.isEmpty())
				subValue = "0";
			film = new FilmfromDB(id, rs.getString(1), rs.getInt(7), 1, 1,
					Integer.parseInt(subValue), rs.getString(3),
					rs.getString(4), rs.getString(5));

			rs.close();

			rs = stm.executeQuery("Select Director.Id, Director.Name from Director, Directs "
					+ "where Director.Id = Directs.Director_Id "
					+ "AND Directs.Film_Id = " + id);
			while (rs.next()) {
				film.addDirector(rs.getInt(1), rs.getString(2), true);
			}
			rs.close();

			rs = stm.executeQuery("Select Film_Actor.Id, Film_Actor.Name ,Film_Play.Character "
					+ "from Film_Actor, Film_Play "
					+ "where Film_Actor.Id = Film_Play.Actor_Id "
					+ "AND Film_Play.Film_Id = " + id);
			while (rs.next()) {
				film.addActor(rs.getInt(1), rs.getString(2), rs.getString(3),
						true);
			}
			rs.close();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return film;
	}

	@Override
	public boolean newActor(String fName, String lName, Statement stm)
			throws TaskRunError {
		try {
			stm.executeUpdate("INSERT INTO Film_Actor(Name, mid) Values('"
					+ (fName + " " + lName) + "','"
					+ System.currentTimeMillis() + "' )");
			System.out.println("ok");
			stm.executeUpdate("INSERT INTO TV_Actor(Name,mid) Values('"
					+ (fName + " " + lName) + "','"
					+ System.currentTimeMillis() + "' )");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return false;
	}

	@Override
	public boolean newCreator(String fName, String lName, Statement stm)
			throws TaskRunError {
		try {
			stm.executeUpdate("INSERT INTO Program_Creator(Name, mid) Values('"
					+ (fName + " " + lName) + "','"
					+ System.currentTimeMillis() + "' )");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return false;
	}

	@Override
	public boolean newDirector(String fName, String lName, Statement stm)
			throws TaskRunError {
		try {
			stm.executeUpdate("INSERT INTO Director(Name, mid) Values('"
					+ (fName + " " + lName) + "','"
					+ System.currentTimeMillis() + "' )");
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}

		return false;
	}

	@Override
	public boolean updateFilm(FilmfromDB filmToupdate, Statement stm)
			throws TaskRunError {
		try {
			String query = "";
			int id = 0;
			if (filmToupdate.getId() == 0) { // we created a new program

				query = "Insert INTO Film(Name,Release_Date, Sequel,Prequel, Country,Estimated_Value,mid) Values("
						+ "'"
						+ filmToupdate.getName()
						+ "', '"
						+ filmToupdate.getYear()
						+ "', '"
						+ filmToupdate.getSequel()
						+ "' ,'"
						+ filmToupdate.getPrequel()
						+ "' ,'"
						+ filmToupdate.getOriginCountry()
						+ "' ,'"
						+ filmToupdate.getValue()
						+ " $', '"
						+ System.currentTimeMillis() + "')";

			} else {
				query = "Update Film SET Release_Date = '"
						+ filmToupdate.getYear() + "' " + "," + " Sequel = '"
						+ filmToupdate.getSequel() + "', " + "Prequel =  '"
						+ filmToupdate.getPrequel() + "', " + "Country = '"
						+ filmToupdate.getOriginCountry()
						+ "', Estimated_Value = '" + filmToupdate.getValue()
						+ " $'  where Id = '" + filmToupdate.getId() + "'";
			}
			stm.executeUpdate(query, new String[] { "Id" });
			ResultSet rs = stm.getGeneratedKeys();
			rs.next();

			if (filmToupdate.getId() == 0)
				id = rs.getInt(1);
			else
				id = 0;

			rs.close();

			for (String q : filmToupdate.getQuery(id, true)) {
				stm.executeUpdate(q);
			}
			for (String q : filmToupdate.getQuery(id, false)) {
				stm.executeUpdate(q);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}

		return false;
	}

	@Override
	public boolean updateProgram(ProgramfromDB programToupdate, Statement stm)
			throws TaskRunError {
		try {
			String query = "";
			int id = 0;
			if (programToupdate.getId() == 0) { // we created a new program

				query = "Insert INTO TV_Program(Name,First_Episode_Date,Final_Episode_Date, Number_of_Episodes, Origin_Country, Number_of_seasons, mid) Values("
						+ "'"
						+ programToupdate.getName()
						+ "', "
						+ programToupdate.getsYear()
						+ ", "
						+ programToupdate.getfYear()
						+ " ,"
						+ programToupdate.getNumOfEpisodes()
						+ " ,'"
						+ programToupdate.getOriganCountry()
						+ "' ,"
						+ programToupdate.getNumOfSeasons()
						+ ", '"
						+ System.currentTimeMillis() + "')";
				System.out.println(query);

			} else {
				query = "Update TV_Program SET  First_Episode_Date = "
						+ programToupdate.getsYear() + " "
						+ ", Final_Episode_Date = "
						+ programToupdate.getfYear() + ", "
						+ "Number_of_Episodes =  "
						+ programToupdate.getNumOfEpisodes() + ", "
						+ "Origin_Country = '"
						+ programToupdate.getOriganCountry()
						+ "', Number_of_seasons = "
						+ programToupdate.getNumOfSeasons() + " where Id = '"
						+ programToupdate.getId() + "'";
			}
			stm.executeUpdate(query, new String[] { "Id" });

			ResultSet rs = stm.getGeneratedKeys();
			rs.next();

			if (programToupdate.getId() == 0)
				id = rs.getInt(1);
			else
				id = 0;

			rs.close();

			for (String q : programToupdate.getQuery(id, true)) {
				stm.executeUpdate(q);
			}
			for (String q : programToupdate.getQuery(id, false)) {
				stm.executeUpdate(q);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}

		return false;
	}

	@Override
	public List<PersonRes> getAllActorsStartWith(String query, Statement stm)
			throws TaskRunError {
		List<PersonRes> actors = new ArrayList<PersonRes>();
		try {
			String querySQL = "select Name, KnownFor from  " +

			"((Select  "
					+ "Film_Actor.Name, Film.Name as KnownFor, Film_Actor.mid "
					+ "from " + "   Film_Actor, " + "  Film, " + " Film_Play "
					+ "where " + "   Film_Actor.Id = Film_Play.Actor_Id "
					+ "      AND Film_Play.Film_Id = Film.Id "
					+ "     and lower(Film_Actor.name) like '"
					+ query.toLowerCase()
					+ "%' "
					+ "group by Film_Actor.Name) union (select  "
					+ "   Name, '' as KnownFor, mid "
					+ "from "
					+ "   Film_Actor "
					+ "where "
					+ "   lower(Film_Actor.name) like '"
					+ query.toLowerCase()
					+ "%')  "
					+ "union  "
					+

					"(Select  "
					+ "   TV_Actor.Name, TV_Program.Name as KnownFor, TV_Actor.mid "
					+ "from "
					+ "   TV_Actor, "
					+ "  TV_Program, "
					+ " TV_Play "
					+ "where "
					+ "   TV_Actor.Id = TV_Play.Actor_Id "
					+ "      AND TV_Play.Program_Id = TV_Program.Id "
					+ "     and lower(TV_Actor.name) like '"
					+ query.toLowerCase()
					+ "%' "
					+ "group by TV_Actor.Name) union (select  "
					+ "   Name, '' as KnownFor, mid "
					+ "from "
					+ "   TV_Actor "
					+ "where "
					+ "   lower(TV_Actor.name) like '"
					+ query.toLowerCase()
					+ "%')) as a " + "group by mid " + "limit 100";

			// System.out.println(querySQL);
			ResultSet rs = stm.executeQuery(querySQL);
			while (rs.next()) {
				actors.add(new PersonRes(1, rs.getString(1), rs.getString(2)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new TaskRunError("Connection Error");
		}
		return actors;
	}
}
